<%-- To be inclued in trainees.jsp, no need error page. --%>
<%-- Save or Update information into database. --%>

<%-- page settings. --%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="com.kha.Trainee"%>

<%-- Global variables declaration. --%>
<%!public final String CONNECTION_URL = "jdbc:mysql://localhost:3306/prepooltest?user=root&;password=";%>
<%!public Connection conn = null;%>
<%!public final String DRIVER = "com.mysql.jdbc.Driver";%>

<%
	// Get trainee bean from session.
	Trainee trainee = (Trainee) session.getAttribute("trainee");

	// Redirect if there are no trainees to be saved.
	if (trainee == null || trainee.isValid() != true) {
		return;
	}
%>

<%
	// DB driver specification.
	Class.forName(DRIVER).newInstance();

	// Get connection with connection string.
	conn = DriverManager.getConnection(CONNECTION_URL);

	// Check if account existed.

	// SQL Statement to be executed.
	Statement stmt = conn.createStatement();
	String statement = "SELECT COUNT(account) FROM trainee WHERE account = '"
			+ trainee.getAccount() + "';";

	ResultSet result = stmt.executeQuery(statement);

	int count = 0;

	// Get result.
	while (result.next()) {
		count = result.getInt(1);
	}

	result.close();
	stmt.close();

	if (count > 0) {
		// Existed a record on db.
		// Updated instead.		

		// SQL Statement to be executed.
		statement = "UPDATE trainee SET firstname=?, lastname=?, email=? WHERE account=?;";
		PreparedStatement pstmt = null;

		// Use to advoid SQL Injection
		pstmt = conn.prepareStatement(statement);

		pstmt.setString(4, trainee.getAccount());
		pstmt.setString(1, trainee.getFirstname());
		pstmt.setString(2, trainee.getLastname());
		pstmt.setString(3, trainee.getEmail());

		pstmt.executeUpdate();

		// Notice user.
		String msg = "Update successfully";
		%>

			<jsp:include page="/WEB-INF/view/info.jsp">
				<jsp:param value="<%=msg%>" name="msg" />
			</jsp:include>

		<%
	
		pstmt.close();

	} else { 
		
		// Insert new record.

		// Use this to avoid injection.

		// SQL Statement to be executed.
		statement = "INSERT INTO trainee(account, firstname, lastname, email) VALUES (?, ?, ?, ?);";
		PreparedStatement pstmt = null;

		// Use to advoid SQL Injection.
		pstmt = conn.prepareStatement(statement);

		pstmt.setString(1, trainee.getAccount());
		pstmt.setString(2, trainee.getFirstname());
		pstmt.setString(3, trainee.getLastname());
		pstmt.setString(4, trainee.getEmail());

		pstmt.executeUpdate();

		pstmt.close();

		// Info user
		String msg = "Save successfully";
		%>

			<jsp:include page="/WEB-INF/view/info.jsp">
				<jsp:param value="<%=msg%>" name="msg" />
			</jsp:include>

		<%
	}

		conn.close();
%>